﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESReportBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();
        #region 工序外协报表
        /// <summary>
        /// 获取工序外协记录信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetSynergicDealInfo(Dictionary<string, string> para) {
            string strSql = @"select distinct ws.workflowstepname,m.mfgordername,m.processno,c.containername,p.description productdesc,st.qty,sc.synergiccompanyname,pe.fullname proposername,
						st.preposerdate,cue.fullname CurrentDealName,coalesce(st.returndate,st.SubmitDate,st.MfgLeaderAuditDate,st.QualityAuditDate,st.TechnicalAuditDate,st.MfgManagerAuditDate) CurrentDealDate,
						st.PlannedComplitionDate,c.plannedstartdate ConPlannedStartDate,c.plannedcompletiondate ConPlannedCompletionDate,
						case when st.returndate is not null then '已转回'   
						when st.submitdate is not null then '转出未转回'
						when st.mfgleaderauditdate is not null then '审核完,未转出'
							when st.qualityauditdate is not null then '质量审核,领导未审核'
								when st.technicalauditdate is not null then '工艺审核,质量未审核'
									when st.MfgManagerAuditDate is not null then '生产经理审核,工艺未审核'
										else '已申请,生产经理未审核'
						end currentStatus from synergicinfo st
						left join container c on c.containerid=st.containerid
                        left join product p on p.productid=c.productid
						left join mfgorder m on m.mfgorderid=c.mfgorderid
						left join synergiccompany sc on sc.synergiccompanyid=st.tofactoryid
						left join employee pe on pe.employeeid=st.proposerid
						left join employee cue on cue.employeeid=coalesce(st.returnemployeeid,st.SubmitEmployeeId,st.MfgLeaderAuditID,st.QualityAuditID,st.TechnicalAuditID,st.MfgManagerAuditID,st.proposerid)
						inner join workflowstep ws on ws.workflowid=st.workflowid and ws.sequence>=st.sequence and ws.sequence<=st.returnsequence where st.isuse=0";

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"])) {
                strSql += $" and c.containername like '%{para["ContainerName"]}%' ";
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += $" and m.ProcessNo like '%{para["ProcessNo"]}%' ";
            }
            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strSql += $" and ws.workflowstepname like '%{para["StepName"]}%' ";
            }

            strSql = string.Concat("select * from (",strSql,")");

            strSql += " order by CurrentDealDate desc";

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strSql).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));

            return result;
        }

        #endregion

        #region 工序报废记录报表
        /// <summary>
        /// 获取工序报废记录报表
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetStepScrapInfo(Dictionary<string, string> para)
        {
            string strSql = @"select distinct m.processno,c.containername,p.description productdesc,c.qty conqty,ws.workflowstepname,sc.qty,ls.lossreasonname,e.fullname,
						sc.submitdate,we.fullname reportname,wt.reportdate,ce.fullname checkname,cc.createdate checkdate,c.plannedstartdate ConPlannedStartDate,
						c.plannedcompletiondate conplannedcompletiondate from scrapinfo sc --order by sc.submitdate desc
						
						left join rejectappinfo rp on rp.id=sc.rejectappinfoid--不合格记录
						left join rejectappproductnoinfo rqp on rqp.rejectappinfoid=rp.id and rqp.disposeresult=30
						left join lossreason ls on ls.lossreasonid=rqp.lossreasonid
						left join rejectappqrinfo rq on rq.rejectappinfoid=rp.id
						left join qualityrecordinfo qd on qd.id=rq.qualityrecordinfoid--质量记录
						left join workreportinfo wt on wt.workreportinfoid=qd.reportinfoid--报工记录
						left join conventioncheckinfo cc on cc.reportinfoid=wt.workreportinfoid--检验记录
						left join container c on c.containerid=wt.containerid
						left join mfgorder m on m.mfgorderid=c.mfgorderid
						left join product p on p.productid=c.productid
						left join workflowstep ws on ws.workflowstepid=wt.workflowstepid
						left join employee e on e.employeeid=sc.submitemployeeid
						left join employee we on we.employeeid=wt.reportemployeeid--操作工
						left join employee ce on ce.employeeid=cc.checkemployeeid--检验人";
            strSql +=Environment.NewLine+ " where 1=1 ";
            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += $" and c.containername like '%{para["ContainerName"]}%' ";
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += $" and m.ProcessNo like '%{para["ProcessNo"]}%' ";
            }
            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strSql += $" and ws.workflowstepname like '%{para["StepName"]}%' ";
            }

            strSql = string.Concat("select * from (", strSql, ")");

            strSql += " order by submitdate desc ";

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strSql).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));

            return result;
        }

        #endregion

        #region 工序报工记录报表
        /// <summary>
        /// 获取工序报工记录报表
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetStepWorkReportInfo(Dictionary<string, string> para)
        {
            string strSql = @"select m.processno,pb.productname,p.description productdesc,c.containername,c.qty conqty,ws.workflowstepname,wt.qty,cci.eligibilityqty,cci.nonsenseqty,s.unitworktime,s.setupworktime,
sum(re.workhours) ReportHours,e.fullname,t.teamname,fa.factoryname,fa.description factorydesc,wt.reportdate,decode(wt.reporttype,0,'首检',3,'工序检',4,'入场检') reporttype,ce.fullname checkName,cci.createdate checkdate from workreportinfo wt
left join container c on c.containerid=wt.containerid
left join mfgorder m on m.mfgorderid=c.mfgorderid
left join product p on p.productid=c.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join workflowstep ws on ws.workflowstepid=wt.workflowstepid
left join conventioncheckinfo cci on cci.reportinfoid=wt.workreportinfoid
left join spec s on s.specid=wt.specid
left join reportemployeeinfo re on re.reportinfoid=wt.workreportinfoid
left join employee e on e.employeeid=wt.reportemployeeid
left join dispatchinfo di on di.id=wt.dispatchinfoid
left join team t on t.teamid=di.teamid
left join factory fa on fa.factoryid=wt.factoryid
left join employee ce on ce.employeeid=cci.checkemployeeid ";
            strSql += Environment.NewLine + " where 1=1 ";
            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += $" and c.containername like '%{para["ContainerName"]}%' ";
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += $" and m.ProcessNo like '%{para["ProcessNo"]}%' ";
            }
            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strSql += $" and ws.workflowstepname like '%{para["StepName"]}%' ";
            }
            strSql += @" group by m.processno,pb.productname,p.description ,c.containername,c.qty ,ws.workflowstepname,wt.qty,cci.eligibilityqty,cci.nonsenseqty,s.unitworktime,s.setupworktime,
e.fullname,t.teamname,fa.factoryname,fa.description ,wt.reportdate,ce.fullname ,cci.createdate,wt.reporttype ";
            strSql = string.Concat("select * from (", strSql, ")");

            strSql += " order by processno,productname ";

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strSql).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));

            return result;
        }
        #endregion

        #region 派工记录报表
        public uMESPagingDataDTO GetContainerDispatchInfo(Dictionary<string, string> para)
        {

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine(@"
                                SELECT di.qty,sb.specname,t.teamname,TO_CHAR(di.plannedcompletiondate,'yyyy-MM-dd') plannedcompletiondate,r.resourcename,
                                      di.id,di.workflowid,di.specid,TO_CHAR(di.dispatchdate,'yyyy-MM-dd') dispatchdate,e.fullname, di.containerid,di.status,    
                                      di.dispatchtype,DECODE(di.dispatchtype,0,'班组派工',1,'任务指派') AS disptype,
                                      c.containername,pb.productname,p.description ProductDesc,m.processno,m.oprno,m.mfgordername,
                                      NVL(f.description,f.factoryname) AS factoryname,c.qty ConQty                                                 
                                FROM dispatchinfo di          
                                LEFT JOIN container c ON c.containerid=di.containerid
                                LEFT JOIN product p ON p.productid=c.productid
                                LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                                LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid 
                                LEFT JOIN spec s ON s.specid = di.specid
                                LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid
                                LEFT JOIN team t ON t.teamid = s.teamid
                                LEFT JOIN factory f ON f.factoryid = s.factoryid
                                LEFT JOIN resourcedef r ON r.resourceid = di.resourceid
                                LEFT JOIN employee e ON e.employeeid = di.dispatchemployeeid
                                WHERE 1 = 1  AND di.dispatchtype = 1");


            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strQuery.AppendLine(" AND LOWER(m.processno) LIKE '%" + para["ProcessNo"].ToLower() + "%' ");
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strQuery.AppendLine(" AND LOWER(c.containername) LIKE '%"+ para["ContainerName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("Product") && !string.IsNullOrWhiteSpace(para["Product"]))
            {
                strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["Product"].ToLower()));
            }

            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strQuery.AppendLine(" AND LOWER(sb.specname) LIKE '%" + para["StepName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("StartDate") && !string.IsNullOrWhiteSpace(para["StartDate"]))
            {
                strQuery.AppendLine(string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
            }

            if (para.ContainsKey("EndDate") && !string.IsNullOrWhiteSpace(para["EndDate"]))
            {
                strQuery.AppendLine(string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
            }

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strQuery.ToString()).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strQuery.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            result.DBTable.Columns.Add("SpecNameDisp");
            result.DBTable.Columns.Add("operator");
            if (result.DBTable.Rows.Count > 0)
            {
                string strID = string.Empty;
                for (int i = 0; i < result.DBTable.Rows.Count; i++)
                {
                    string strName = result.DBTable.Rows[i]["SpecName"].ToString();
                    result.DBTable.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                    if (strID.Contains("'" + result.DBTable.Rows[i]["id"].ToString() + "',") == false)
                    {
                        strID += "'" + result.DBTable.Rows[i]["id"].ToString() + "',";
                    }
                }

                if (strID != "")
                {
                    strID = strID.TrimEnd(',');
                    DataTable dtOperator = GetDispatchEmployeeInfo(strID);
                    if (dtOperator.Rows.Count > 0)
                    {
                        for (int i = 0; i < result.DBTable.Rows.Count; i++)
                        {
                            DataRow[] dr = dtOperator.Select("dispatchinfoid ='" + result.DBTable.Rows[i]["id"].ToString() + "'");
                            string strOperator = string.Empty;
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (strOperator.Contains(dr1["fullname"].ToString()) == false)
                                    {
                                        strOperator += dr1["fullname"].ToString() + ",";
                                    }
                                }

                                if (strOperator != "")
                                {
                                    strOperator = strOperator.TrimEnd(',');
                                }
                            }

                            result.DBTable.Rows[i]["operator"] = strOperator;
                        }
                    }

                }
            }

            return result;
        }

        /// <summary>
        /// 获取加工人员信息
        /// </summary>
        /// <param name="strDispatchinfoID"></param>
        /// <returns></returns>
        public DataTable GetDispatchEmployeeInfo(string strDispatchinfoID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT dei.dispatchinfoid,dei.employeeid,e.fullname,e.employeename
                        FROM dispatchemployeeinfo dei
                        LEFT JOIN employee e ON e.employeeid = dei.employeeid
                        WHERE dei.dispatchinfoid IN (" + strDispatchinfoID + ")");

            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        #endregion

        #region 关重件数据采集报表
        public uMESPagingDataDTO GetDataCollectionInfo(Dictionary<string, string> para)
        {

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine(@"
                                SELECT  dc.*, dcd.productno,dcd.collectqty,dcd.checkvalue,dcd.collecttype,
                                       ci.checkiteminfoname,ci.checkitem,dcd.checkitemid,dcd.SequenceNum,dcd.checkvalue1,
                                       sb.specname,dc.description AS ProductDesc,c.qty AS conQty
                                FROM DataCollectInfo dc
                                LEFT JOIN container c ON c.containerid=dc.containerid
                                LEFT JOIN spec s ON s.specid = dc.specid
                                LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid
                                LEFT JOIN DataCollectDetailInfo dcd ON dc.id = dcd.datacollectinfoid
                                LEFT JOIN checkiteminfo ci ON ci.checkiteminfoid = dcd.checkitemid
                                WHERE 1 = 1");


            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strQuery.AppendLine(" AND LOWER(dc.processno) LIKE '%" + para["ProcessNo"].ToLower() + "%' ");
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strQuery.AppendLine(" AND LOWER(dc.containername) LIKE '%" + para["ContainerName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("Product") && !string.IsNullOrWhiteSpace(para["Product"]))
            {
                strQuery.AppendLine(string.Format("AND (LOWER(dc.productname) LIKE '%{0}%' OR LOWER(dc.description) LIKE '%{0}%')", para["Product"].ToLower()));
            }

            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strQuery.AppendLine(" AND LOWER(sb.specname) LIKE '%" + para["StepName"].ToLower() + "%' ");
            }

            strQuery.AppendLine("ORDER BY dcd.sequencenum ");
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strQuery.ToString()).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strQuery.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            result.DBTable.Columns.Add("SpecNameDisp");
            result.DBTable.Columns.Add("checktype");
            if (result.DBTable.Rows.Count > 0)
            {
                for (int i = 0; i < result.DBTable.Rows.Count; i++)
                {
                    string strName = result.DBTable.Rows[i]["SpecName"].ToString();
                    result.DBTable.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                    if (!string.IsNullOrEmpty(result.DBTable.Rows[i]["collecttype"].ToString()))
                    {
                        if (result.DBTable.Rows[i]["collecttype"].ToString() == "1")
                        {
                            result.DBTable.Rows[i]["checktype"] = "关键工序";
                        }
                        if (result.DBTable.Rows[i]["collecttype"].ToString() == "2")
                        {
                            result.DBTable.Rows[i]["checktype"] = "首件检验";
                        }
                        if (result.DBTable.Rows[i]["collecttype"].ToString() == "3")
                        {
                            result.DBTable.Rows[i]["checktype"] = "工序检验";
                        }
                        if (result.DBTable.Rows[i]["collecttype"].ToString() == "4")
                        {
                            result.DBTable.Rows[i]["checktype"] = "入厂检验";
                        }
                    }
                }
            }

            return result;
        }


        #endregion

        #region 工时记录报表

        public uMESPagingDataDTO GetReportWorkHoursInfo(Dictionary<string, string> para)
        {
            string strSql = @" from workreportinfo wt
left join container c on c.containerid=wt.containerid
left join mfgorder m on m.mfgorderid=c.mfgorderid
left join product p on p.productid=c.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join workflowstep ws on ws.workflowstepid=wt.workflowstepid
left join spec s on s.specid=wt.specid
left join reportemployeeinfo re on re.reportinfoid=wt.workreportinfoid
left join employee e on e.employeeid=wt.reportemployeeid--报工人
left join employee e2 on e2.employeeid=re.employeeid--工人
left join dispatchinfo di on di.id=wt.dispatchinfoid
left join team t on t.teamid=di.teamid
left join factory fa on fa.factoryid=wt.factoryid ";
            strSql += Environment.NewLine + " where 1=1 ";
            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += $" and c.containername like '%{para["ContainerName"]}%' ";
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += $" and m.ProcessNo like '%{para["ProcessNo"]}%' ";
            }
            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strSql += $" and ws.workflowstepname like '%{para["StepName"]}%' ";
            }
            if (para.ContainsKey("WorkerName") && !string.IsNullOrWhiteSpace(para["WorkerName"]))
            {
                strSql += $" and e2.fullname like '%{para["WorkerName"]}%' ";
            }
            if (para.ContainsKey("TeamID") && !string.IsNullOrWhiteSpace(para["TeamID"]))
            {
                strSql += $" and t.TeamID = '{para["TeamID"]}' ";
            }
            if (para.ContainsKey("FactoryID") && !string.IsNullOrWhiteSpace(para["FactoryID"]))
            {
                strSql += $" and fa.FactoryID = '{para["FactoryID"]}' ";
            }
            if (para.ContainsKey("StartDate") && !string.IsNullOrWhiteSpace(para["StartDate"]))
            {
                strSql += $" and wt.reportdate >=TO_DATE('{para["StartDate"]} 00:00:00','yyyy-MM-dd HH24:MI:SS')  ";
            }
            if (para.ContainsKey("EndtDate") && !string.IsNullOrWhiteSpace(para["EndtDate"]))
            {
                strSql += $" and wt.reportdate <=TO_DATE('{para["EndtDate"]} 23:59:59','yyyy-MM-dd HH24:MI:SS')  ";
            }

            string selectSql = @" select m.processno,pb.productname,p.description productdesc,c.containername,c.qty conqty,ws.workflowstepname,sum( wt.qty) qty,
s.unitworktime,s.setupworktime,
sum(re.workhours) ReportHours,e.fullname,e2.fullname workerName,t.teamname,
fa.factoryname,fa.description factorydesc,decode(wt.reporttype,0,'首检',3,'工序检',4,'入场检') reporttype,wt.reportdate ";

            string groupBy = @" group by m.processno,pb.productname,p.description ,c.containername,c.qty ,ws.workflowstepname,s.unitworktime,s.setupworktime,
e.fullname,e2.fullname,t.teamname,fa.factoryname,fa.description ,wt.reporttype,wt.reportdate ";

            string sql = "";
            sql = string.Concat("select * from (", selectSql, strSql, groupBy, ")");
            sql += " order by processno,productname,reportdate ";
            
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(sql).Tables[0];
            else
            {
                result = OracleHelper.GetPagingDataIns(sql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
                if (result.DBTable.Rows.Count > 0) { 
                    result.DBTable.Columns.Add("AllReportHours");
                selectSql = " select sum(nvl(re.workhours,0)) AllReportHours ";
                var temp = OracleHelper.GetSingle(selectSql+strSql);
                result.DBTable.Rows[0]["AllReportHours"] = temp.ToString();
                }
            }

            return result;
        }

        #endregion

        #region 返工返修记录报表
        public uMESPagingDataDTO GetContainerRepairInfo(Dictionary<string, string> para)
        {

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine(@"
                                SELECT r.qty,sb.specname,TO_CHAR(r.oprdate,'yyyy-MM-dd') oprdate,e.fullname, r.containerid,
                                       c.containername,pb.productname,p.description ProductDesc,m.processno,m.oprno,m.mfgordername,
                                       c.qty ConQty ,r.repaircontainername 
                                FROM repairinfo r
                                LEFT JOIN container c ON c.containerid=r.containerid
                                LEFT JOIN product p ON p.productid=c.productid
                                LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                                LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid 
                                LEFT JOIN spec s ON s.specid = r.specid
                                LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid
                                LEFT JOIN employee e ON e.employeeid = r.opremployeeid
                                WHERE 1 = 1 ");


            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strQuery.AppendLine(" AND LOWER(m.processno) LIKE '%" + para["ProcessNo"].ToLower() + "%' ");
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strQuery.AppendLine(" AND LOWER(c.containername) LIKE '%" + para["ContainerName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("Product") && !string.IsNullOrWhiteSpace(para["Product"]))
            {
                strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["Product"].ToLower()));
            }

            if (para.ContainsKey("StepName") && !string.IsNullOrWhiteSpace(para["StepName"]))
            {
                strQuery.AppendLine(" AND LOWER(sb.specname) LIKE '%" + para["StepName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("StartDate") && !string.IsNullOrWhiteSpace(para["StartDate"]))
            {
                strQuery.AppendLine(string.Format("AND r.oprdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
            }

            if (para.ContainsKey("EndDate") && !string.IsNullOrWhiteSpace(para["EndDate"]))
            {
                strQuery.AppendLine(string.Format("AND r.oprdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
            }

            strQuery.AppendLine("ORDER BY r.oprdate DESC");

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strQuery.ToString()).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strQuery.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            result.DBTable.Columns.Add("SpecNameDisp");
            for (int i = 0; i < result.DBTable.Rows.Count; i++)
            {
                string strName = result.DBTable.Rows[i]["SpecName"].ToString();
                result.DBTable.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);
            }

            return result;
        }


        #endregion

        #region 临时工艺工时维护报表
        public uMESPagingDataDTO GetSpecWorkHoursChangeInfo(Dictionary<string, string> para)
        {

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine(@"select wc.productname,p.description ProductDesc,wb.workflowname,w.workflowrevision,sb.specname,t.teamname,fa.factoryname,wc.unitworktime,
wc.setupworktime,wc.tempunitworktime,wc.tempsetupworktime,e.fullname GYY,ch.lastchangedate CreateWorkflowDate,e2.fullname DEY,
wc.operatedate specworkhourschangeDate from workhourschangerecord wc
left join productbase pb on pb.productname=wc.productname
left join product p on p.productid=pb.revofrcdid
left join workflow w on w.workflowid=wc.workflowid
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
left join spec s on s.specid=wc.specid
left join specbase sb on sb.specbaseid=s.specbaseid
left join team t on t.teamid=s.teamid
left join factory fa on fa.factoryid=s.factoryid
left join employee e on e.employeeid=w.createemployeeid
left join employee e2 on e2.employeeid=wc.operateemployeeid
left join changestatus ch on ch.changestatusid=w.changehistoryid
                                WHERE 1 = 1 ");

            
            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' )", para["Product"].ToLower()));
            }

            if (para.ContainsKey("SpecName") && !string.IsNullOrWhiteSpace(para["SpecName"]))
            {
                strQuery.AppendLine(" AND LOWER(sb.specname) LIKE '%" + para["SpecName"].ToLower() + "%' ");
            }

            if (para.ContainsKey("GYY") && !string.IsNullOrWhiteSpace(para["GYY"]))
            {
                strQuery.AppendFormat(" and e.fullname like '%{0}%' ",para["GYY"]);
            }

            if (para.ContainsKey("DEY") && !string.IsNullOrWhiteSpace(para["DEY"]))
            {
                strQuery.AppendFormat(" and e2.fullname like '%{0}%' ", para["DEY"]);
            }

            if (para.ContainsKey("StartDate") && !string.IsNullOrWhiteSpace(para["StartDate"]))
            {
                strQuery.AppendFormat(" AND wc.operatedate>TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') ", para["StartDate"]);
            }

            if (para.ContainsKey("EndDate") && !string.IsNullOrWhiteSpace(para["EndDate"]))
            {
                strQuery.AppendFormat("AND wc.operatedate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]);
            }

            strQuery.AppendLine("ORDER BY wc.operatedate asc");

            uMESPagingDataDTO result = new uMESPagingDataDTO();
            if (para["CurrentPageIndex"] == "all")
                result.DBTable = OracleHelper.Query(strQuery.ToString()).Tables[0];
            else
                result = OracleHelper.GetPagingDataIns(strQuery.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
           // result.DBTable.Columns.Add("SpecNameDisp");
            //for (int i = 0; i < result.DBTable.Rows.Count; i++)
            //{
            //    string strName = result.DBTable.Rows[i]["SpecName"].ToString();
            //    result.DBTable.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);
            //}

            return result;
        }


        #endregion
    }
}
